﻿CREATE PROCEDURE [dbo].[usp_select_AuditLog]
(
	@ActionName			varchar(25)
    ,@MessageID			varchar(38)		= NULL
	,@FaultID			varchar(38)		= NULL
	,@AuditUserName		varchar(256)	= NULL
	,@StartDate			datetime		= NULL
    ,@EndDate			datetime		= NULL
	,@Application		varchar(256)	= '-1'
	,@Debug				bit				= 0
)
AS

/****************************************************************************
Name: usp_select_AuditLog.sql
Description: Selects the data from the audit log table.  Allows filtering by
			 MessageID, UserName, and time range.
*****************************************************************************/
SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10);
DECLARE @ErrorCode         int;

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: ';


-- Test harness

-- declare the stored procedure paramters
/*
DECLARE  @MessageID	varchar(256)
DECLARE  @AuditUserName  varchar(256)
DECLARE  @StartDate datetime
DECLARE  @EndDate   datetime
DECLARE  @Debug bit

-- now set them
SELECT
	 @MessageID	= NULL
    ,@AuditUserName = NULL
    ,@StartDate = NULL
    ,@EndDate = NULL
    ,@Debug     = 1
*/


-- Print parameters
/*IF (@Debug > 0)
BEGIN
    PRINT @DebugPrefix + '@MessageID = ' + CAST(@MessageID AS varchar)
	PRINT @DebugPrefix + '@AuditUserName = ' + CAST(@AuditUserName AS varchar)
	PRINT @DebugPrefix + '@StartDate = ' + CAST(@StartDate AS varchar)
	PRINT @DebugPrefix + '@EndDate = ' + CAST(@EndDate AS varchar)
END;*/

SELECT
	 al.AuditLogID
	,at.ActionTypeID
	,at.ActionName
	,al.MessageID
	,alm.MessageData
	,al.NativeMessageID
	,al.ContentType
	,al.MessageName
	,al.FaultID
	,al.NativeFaultID
	,al.Application
	,al.ServiceName
	,al.ResubmitURL
	,al.ResubmitCode
	,al.ResubmitMessage
	,al.AuditDate
	,al.AuditUserName

FROM
	AuditLog al 
	INNER JOIN ActionType at ON al.ActionTypeID = at.ActionTypeID
	LEFT JOIN AuditLogMessageData alm ON al.AuditLogID = alm.AuditLogID

WHERE
	at.ActionName			 = ISNULL(@ActionName, at.ActionName)
	AND (@MessageID IS NULL OR	al.MessageID = @MessageID)
	AND (@FaultID IS NULL OR	al.FaultID = @FaultID)
	AND al.AuditUserName	 = ISNULL(@AuditUserName, al.AuditUserName)
	AND al.AuditDate		>= ISNULL(@StartDate, al.AuditDate)
	AND al.AuditDate		<= ISNULL(@EndDate, al.AuditDate)
	AND ((@Application = '-1') OR al.Application = @Application)

ORDER BY
	al.AuditDate DESC

-- CHECK FOR ERROR
SET @ErrorCode = @@Error;
IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;
			
RETURN 0;

-- Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
    IF (@Debug > 0) 
		BEGIN
			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
			RETURN @ErrorCode
		END

    -- Don't return any evidence of what specific error occurred, i.e. 
    -- we want to ward off potential hackers.
    RETURN 1;
END;

SET NOCOUNT OFF;
